﻿Imports Microsoft.Office.Interop
Imports MySql.Data.MySqlClient

Public Class frmadd_container
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1

    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader


    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader
    Dim arrayspilt() As String
    Private Structure ExcelRows

        Dim C1 As String
        Dim C2 As String
        Dim C3 As String
        Dim C4 As String
        Dim C5 As String

    End Structure
    Structure shipper
        Dim id As String
        Dim name As String
    End Structure
    Dim insertshipper() As shipper
    Dim count As Integer = 0
    Dim max As Integer
    Private ExcelRowlist As List(Of ExcelRows) = New List(Of ExcelRows)
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)




    End Sub
    Public Sub add_voyage()


        txt_voyage.Text = frmsearch_addcontainer.idvoyage
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select COUNT(SHIPPERID) from shipper;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                ReDim insertshipper(mySqlReader("COUNT(SHIPPERID)"))
                max = mySqlReader("COUNT(SHIPPERID)")
            End While



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from shipper;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                insertshipper(count).id = mySqlReader("SHIPPERID")
                If mySqlReader("SHIPNICKNAME") IsNot DBNull.Value Then

                    insertshipper(count).name = mySqlReader("SHIPNICKNAME")
                Else
                    insertshipper(count).name = ""
                End If

                count = count + 1
            End While



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()





    End Sub
    Private Function getInfo() As Boolean
        ExcelRowlist.Clear()
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        Dim Excel As New Excel.Application
        If TextBox1.Text <> Nothing Then

            Excel.Workbooks.Open(TextBox1.Text)


            Excel.Sheets(arrayspilt(0)).Activate()
            Excel.Range("A4").Activate()

            Dim row As New ExcelRows

            Do
                If Excel.ActiveCell.Value > Nothing Or Excel.ActiveCell.Text > Nothing Then


                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C1 = Trim(Excel.ActiveCell.Value)

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C2 = Trim(Excel.ActiveCell.Value)

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C3 = Trim(Excel.ActiveCell.Value)

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C4 =Trim(Excel.ActiveCell.Value)
                    Excel.ActiveCell.Offset(0, 2).Activate()
                    row.C5 = Trim(Excel.ActiveCell.Value)





                    ExcelRowlist.Add(row)

                    Excel.ActiveCell.Offset(1, -6).Activate()


                Else

                    Exit Do

                End If

            Loop

            Return True
        Else


        End If
        Excel.Quit()
        Return True
    End Function
    Public Sub getexcel_new()

        ListView1.Items.Clear()

        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet1 As Excel.Worksheet
        Dim i As Integer
        xlBook = xlApp.Workbooks.Open(TextBox1.Text)
        'xlBook.Application.Visible = False
        xlSheet1 = xlBook.Worksheets(1)

        i = 8
        Do While Not Trim(xlSheet1.Cells.Item(i, 1).Value) = ""
            '*** Rows ***'
            Try

                With ListView1.Items.Add(xlSheet1.Cells.Item(i, 2).value.ToString)
                    If xlSheet1.Cells.Item(i, 3).Value <> "" Then
                        .SubItems.Add(xlSheet1.Cells.Item(i, 3).value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If


                    If xlSheet1.Cells.Item(i, 4).Value <> "" Then

                        .SubItems.Add(xlSheet1.Cells.Item(i, 4).value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If


                    If xlSheet1.Cells.Item(i, 5).Value <> "" Then
                        .SubItems.Add(xlSheet1.Cells.Item(i, 5).Value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If



                    If CStr(xlSheet1.Cells.Item(i, 7).Value) <> "" Then
                        .SubItems.Add(xlSheet1.Cells.Item(i, 7).Value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If


                    If CStr(xlSheet1.Cells.Item(i, 8).Value) <> "" Then
                        .SubItems.Add(xlSheet1.Cells.Item(i, 8).Value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If



                    If CStr(xlSheet1.Cells.Item(i, 9).Value) <> "" Then
                        .SubItems.Add(xlSheet1.Cells.Item(i, 9).Value.ToString)
                    Else
                        .SubItems.Add(" ")
                    End If

                End With

            Catch ex As Exception
            End Try

            i = i + 1
        Loop
        '*** Quit and Clear Object ***'
        xlBook.Close()
        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)


        xlSheet1 = Nothing
        xlBook = Nothing
        xlApp = Nothing


        Dim proc As System.Diagnostics.Process

        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            proc.Kill()
        Next
        txt_count.Text = ListView1.Items.Count.ToString
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


        ListView1.Items.Clear()


        If getInfo() Then
            For Each Xitem In ExcelRowlist
                Dim ivite As ListViewItem
                ivite = Me.ListView1.Items.Add(Xitem.C1)
                ivite.SubItems.AddRange(New String() {Xitem.C2, Xitem.C3, Xitem.C4, Xitem.C5})
            Next
        End If
        txt_count.Text = ListView1.Items.Count.ToString

    End Sub



    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click


        Dim idshipper As String = " "
        Dim checkshipper As Integer = 0
        For counter1 = 0 To ListView1.Items.Count - 1
            Try
                For count2 = 0 To max - 1
                    If Trim(ListView1.Items(counter1).SubItems(4).Text) <> "" Then
                        If Trim(insertshipper(count2).name) = Trim(ListView1.Items(counter1).SubItems(4).Text) Then
                            idshipper = insertshipper(count2).id
                            checkshipper = 1
                            Exit For
                        Else
                            idshipper = " "
                        End If

                    End If

                Next
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
          
                If checkshipper = 1 Then
                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try
                        mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into ctnmain ( CTNSTRING, CTNSIZE, CTNAGENT, CTNSTAT,CTNCONSI,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn,@ctnshipname,@ctnbooking,@ctnyard);SELECT LAST_INSERT_ID();"
                        mySqlCommand.Connection = mysql
                        mySqlCommand.Parameters.AddWithValue("@ctnstring", ListView1.Items(counter1).SubItems(0).Text)
                        mySqlCommand.Parameters.AddWithValue("@ctnsize", ListView1.Items(counter1).SubItems(1).Text)
                        mySqlCommand.Parameters.AddWithValue("@ctnagent", ListView1.Items(counter1).SubItems(2).Text)
                        mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                        mySqlCommand.Parameters.AddWithValue("@ctnconsi", idshipper)
                    mySqlCommand.Parameters.AddWithValue("@ctnvoyn", frmsearch_addcontainer.idvoyage)

                    mySqlCommand.Parameters.AddWithValue("@ctnshipname", ListView1.Items(counter1).SubItems(4).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnbooking", ListView1.Items(counter1).SubItems(5).Text)

                    mySqlCommand.Parameters.AddWithValue("@ctnyard", ListView1.Items(counter1).SubItems(6).Text)
                        mySqlCommand.ExecuteNonQuery()
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()
                checkshipper = 0

            ElseIf Trim(ListView1.Items(counter1).SubItems(4).Text) = "DEPOT" Then

                mysql.Close()
                Dim idlast As String
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into ctnmain ( CTNSTRING, CTNSIZE, CTNAGENT, CTNSTAT,CTNCONSI,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn,@ctnshipname,@ctnbooking,@ctnyard);SELECT LAST_INSERT_ID();"
                    mySqlCommand.Connection = mysql
                    mySqlCommand.Parameters.AddWithValue("@ctnstring", ListView1.Items(counter1).SubItems(0).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnsize", ListView1.Items(counter1).SubItems(1).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnagent", ListView1.Items(counter1).SubItems(2).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnstat", "0")
                    mySqlCommand.Parameters.AddWithValue("@ctnconsi", idshipper)
                    mySqlCommand.Parameters.AddWithValue("@ctnvoyn", frmsearch_addcontainer.idvoyage)

                    mySqlCommand.Parameters.AddWithValue("@ctnshipname", ListView1.Items(counter1).SubItems(4).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnbooking", ListView1.Items(counter1).SubItems(5).Text)

                    mySqlCommand.Parameters.AddWithValue("@ctnyard", ListView1.Items(counter1).SubItems(6).Text)

                    idlast = mySqlCommand.ExecuteScalar()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()


                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into ctndepot ( CTNID, CTNDATEIN, CTNSTATDEPOT, CTNDAYRE,CTNDEDATES) values (@ctnid,@ctndatein,@ctnstat,@ctndayre,@CTNDEDATES);"
                    mySqlCommand.Connection = mysql
                    mySqlCommand.Parameters.AddWithValue("@ctnid", idlast)
                    mySqlCommand.Parameters.AddWithValue("@ctndatein", "0")
                    mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                    mySqlCommand.Parameters.AddWithValue("@ctndayre", "0")

                    mySqlCommand.Parameters.AddWithValue("@CTNDEDATES", Date.Now.ToString("dd-MM-yyyy"))
                    mySqlCommand.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()


            Else
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into ctnmain ( CTNSTRING, CTNSIZE, CTNAGENT, CTNSTAT,CTNCONSI,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn,@ctnshipname,@ctnbooking,@ctnyard)"
                    mySqlCommand.Connection = mysql
                    mySqlCommand.Parameters.AddWithValue("@ctnstring", ListView1.Items(counter1).SubItems(0).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnsize", ListView1.Items(counter1).SubItems(1).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnagent", ListView1.Items(counter1).SubItems(2).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                    mySqlCommand.Parameters.AddWithValue("@ctnconsi", idshipper)
                    mySqlCommand.Parameters.AddWithValue("@ctnvoyn", frmsearch_addcontainer.idvoyage)

                    mySqlCommand.Parameters.AddWithValue("@ctnshipname", ListView1.Items(counter1).SubItems(4).Text)
                    mySqlCommand.Parameters.AddWithValue("@ctnbooking", ListView1.Items(counter1).SubItems(5).Text)

                    mySqlCommand.Parameters.AddWithValue("@ctnyard", ListView1.Items(counter1).SubItems(6).Text)

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

            End If




            checkshipper = 0
        Next

        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from ctnmain where CTNVOYN = '" & CInt(frmsearch_addcontainer.idvoyage) & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())


                mysql1.Close()
                If mysql1.State = ConnectionState.Closed Then
                    mysql1.Open()
                End If
                Try
                    mySqlCommand1.Parameters.Clear()
                    mySqlCommand1.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                    mySqlCommand1.Connection = mysql1
                    mySqlCommand1.Parameters.AddWithValue("@rptctn", mySqlReader("CTNSTRING"))
                    mySqlCommand1.Parameters.AddWithValue("@rptstat", "MA")
                    mySqlCommand1.Parameters.AddWithValue("@rptctndate", frmsearch_addcontainer.datethai)
                    mySqlCommand1.Parameters.AddWithValue("@rptctndatetpe", frmsearch_addcontainer.datepenang)
                    If mySqlReader("CTNSIZE") = "40'HC" Then
                        mySqlCommand1.Parameters.AddWithValue("@rpttype", "45G1")
                    ElseIf mySqlReader("CTNSIZE") = "20'GP" Then
                        mySqlCommand1.Parameters.AddWithValue("@rpttype", "22G1")
                    End If



                    mySqlCommand1.Parameters.AddWithValue("@rptidctn", mySqlReader("CTNMAINID"))
                    mySqlCommand1.Parameters.AddWithValue("@rptagent", mySqlReader("CTNAGENT"))
                    mySqlCommand1.Parameters.AddWithValue("@rptdatetype1", frmsearch_addcontainer.timehhmm)
                    mySqlCommand1.ExecuteNonQuery()
                    mysql1.Close()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try






            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()







        MsgBox("บันทึกข้อมูลเรียบร้อย")
        ListView1.Items.Clear()

    End Sub


    Private Sub frmadd_container_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        add_voyage()





    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        getexcel_new()
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        With OpenFileDialog1
            .Filter = "Excel File  (*.xlsx) | *.xls"
            If (.ShowDialog() = DialogResult.OK) Then
                Dim fi As New System.IO.FileInfo(.FileName)
                TextBox1.Text = .FileName
                arrayspilt = Split(fi.Name, "_")
            End If
        End With


    End Sub

End Class